create table articleType (
   id               number(10) primary key,
   typeName         varchar2(50),		
   parentId         number(10),		--为0为父节点，为id时为子节点，id为父节点的id
   url				      varchar2(200),
   --level          number(10) default 1,--如果是多级（二级以上），还需要加上该字段，为0时是顶级父节点，1，2，3为1，2，3级...
   remark           varchar2(100)
);

create sequence articleType_id minvalue 0 start with 1 increment by 1;

insert into articleType values(articleType_id.nextval,'前端',0,'#',null);
insert into articleType values(articleType_id.nextval,'原生JS',2,'#',null);
insert into articleType values(articleType_id.nextval,'HTML5',2,'#',null);
insert into articleType values(articleType_id.nextval,'CSS3',2,'#',null);

insert into articleType values(articleType_id.nextval,'Java',0,'#',null);
insert into articleType values(articleType_id.nextval,'JavaCore',6,'articleType.servlet?typeId=',null);
insert into articleType values(articleType_id.nextval,'JavaWeb',6,'#',null);

insert into articleType values(articleType_id.nextval,'框架技术',0,'#',null);
insert into articleType values(articleType_id.nextval,'spring',9,'#',null);
insert into articleType values(articleType_id.nextval,'mybatis',9,'#',null);

select * from articleType;



create table article 
(
   id                 number(10)   not null,
   title              varchar2(200),   --文章标题
   content            CLOB,            --文章内容
   publishTime        date,            --发布时间
   click              number(10),      --阅读量
   likeNumber		  number(5),           --点赞量
   typeId             number(10),       --foreign key articleType id
   userId             number(10),       --foreign key users id
   top                number(3),        --是否置顶
   support            number(3),        --是推荐
   constraint PK_ARTICLE primary key (id)
);

alter table article
   add constraint FK_ARTICLE_REFERENCE_ARTICLET foreign key (typeId)
      references articleType (id);

alter table article
   add constraint FK_ARTICLE_REFERENCE_USERS foreign key (userId)
      references users (id);
      
create sequence article_id minvalue 0 start with 1 increment by 1;

insert into article values(article_id.nextval,'第一篇测试文章','java Core类文件测试测试',sysdate,0,0,7,1,0,0);
insert into article values(article_id.nextval,'第二篇测试文章','java Core类文件测试测试',sysdate,0,0,7,1,0,0);

select * from article;

--获得完整的文章信息
select a.id,a.title,to_char(a.content) content,a.publishtime,a.click,a.likenumber,at1.typename subname,at2.typename parentname,u.name username,a.top,a.support
from Article a left outer join ArticleType at1 on a.typeId=at1.id
 left outer join ArticleType at2 on at1.parentId=at2.id
 left outer join users u on a.userid=u.id where typeId=7; 